﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
using System.Web.UI.WebControls;// Pass value as WebControls eg dropdownlist

/// <summary>
/// Summary description for dbConn
/// </summary>
public class dbConn
{
	public dbConn()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    //Add for Connection

    private static string ConnectionString
    {

        get
        {
            return ConfigurationManager.ConnectionStrings["prod"].ConnectionString;  // Add reference using System.Configuration;
            // return ConfigurationManager.ConnectionStrings["dev"].ConnectionString;
        }
    }



    // Function for find the  Corresponding columnName/Value for particulare % range

     string colTitle;

    public string getColName(double  gpaValue)
        
    {
        string colName;
        if (gpaValue >= 2.00 && gpaValue <= 2.24)
        {

            colName = "x200224x";
            colTitle = colName;
        }
        else if (gpaValue >= 2.25 && gpaValue <= 2.49)
        {
            colName = "x225249x";
            colTitle = colName;
        }
        else if (gpaValue >= 2.50 && gpaValue <= 2.74)
        {
            colName = "x250274x";
            colTitle = colName;
        }

        else if (gpaValue >= 2.75 && gpaValue <= 2.99)
        {
            colName = "x275299x";
            colTitle = colName;

        }

        else if (gpaValue >= 3.00 && gpaValue <= 3.24)
        {
            colName = "x30324x";
            colTitle = colName;

        }
        else if (gpaValue >= 3.25 && gpaValue <= 3.49)
        {
            colName = "x325349x";
            colTitle = colName;

        }
        else if (gpaValue >= 3.50 && gpaValue <= 3.74)
        {
            colName = "x350374x";
            colTitle = colName;

        }
        else if (gpaValue >= 3.75)
        {
            colName = "x37540x";
            colTitle = colName;
        }
        return colTitle;


    }



    // Function to find the SAT Value from the Table

    string SatVal;
    public string findSATVal(string sat, string col)
    {


        string sqlSAT = "SELECT " + col + " AS SATVal FROM dbo.scholarship_matrix_2012 WHERE (sat_score = '" + sat + "')";
        SqlDataReader read;
        SqlConnection dbConn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sqlSAT, dbConn);
        try
        {

            dbConn.Open();

            read = cmd.ExecuteReader();
            while (read.Read())
            {
                SatVal += (Convert.ToString(read["SATVal"]));


            }
            return SatVal;
            //read.Close();
        }

        finally
        {

            dbConn.Close();

        }

    }

    // Function to find the ACT Value from the Table
    string ActVal;
    public string findACTVal(string act, string col)
    {

        string sqlACT = "SELECT " + col + " as ACTVal FROM dbo.scholarship_matrix_2012 WHERE (act_score = '" + act + "')";
        SqlDataReader rdr;
        SqlConnection dbConn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sqlACT, dbConn);
        try
        {
            dbConn.Open();
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                ActVal = rdr["ACTVal"].ToString();
            }
            rdr.Close();

        }
        finally
        {
            dbConn.Close();

        }


        return ActVal;




    }



    // Check your value which one is greater 

    int estVal;
    public int estValue(int val1, int val2)
    {
        if (val1 > val2)
            estVal = val1;
        else if (val1 < val2)
            estVal = val2;
        else if (val1 == val2 && val1 != 0)
            estVal = val1;
        else
            estVal = 0;
        return estVal;

    }

    // Populate dd from db table
    public void ddPopulate(DropDownList dd, string sql)
    {

        SqlDataReader reader;
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            dd.DataSource = reader;
            dd.DataValueField = "col1";
            dd.DataTextField = "col2";
            dd.DataBind();
            //reader.Close();

        }
        finally
        {
            conn.Close();

        }

    }







}
